package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.RecordCourse;
import com.xmy.cultivate.entity.Staff;
import com.xmy.cultivate.entity.StudentGrade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.ShiftList;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 学生所在班级与课程 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-09
 */
public interface StudentGradeMapper extends BaseMapper<StudentGrade> {


    /**
     * 获取最后一次分班数据
     * @return
     */
    @Select("select * from student_grade where school_id =#{schoolId} and subjects_id = #{subjectsId} and student_id = #{studentId} and year_part=#{yearPart} and quarter_num=#{quarterNum} and deleted=0 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForStudent(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);



    /**
     * 获取最后一次分班数据
     * @return
     */
    @Select("select * from student_grade where order_detail_id = #{orderDetailId} and deleted=0 and reading_status<>3 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForOrderDetailId(@Param("orderDetailId") Long orderDetailId);


    @Select("select a.id,a.reading_status,getCourseNameForId(b.course_id) as courseName,getSchoolName(a.school_id) as schoolName,b.name as gradeName,getStaffNameForId(b.teacher_id) as teacherName,c.`name` as studentName,c.linkman,c.linkman_phone from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id left JOIN student c on a.student_id = c.id ${ew.customSqlSegment}")
    public abstract IPage<ShiftList> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(id) as count from student_grade where student_id = #{studentId} and grade_id = #{gradeId} and status = 1 and reading_status = 1")
    public abstract Integer studentIsAtGrade(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId);

    @Select("select count(DISTINCT student_id) as count from student_grade where school_id = #{schoolId} and grade_id = #{gradeId} and status = 1 and reading_status = 1 and deleted=0")
    public abstract Integer studentCount(@Param("schoolId") Long schoolId,@Param("gradeId") Long gradeId);

    @Select("select c.`name` from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id LEFT JOIN staff as c on b.teacher_id = c.id where a.student_id =#{studentId} and a.subjects_id=#{subjectsId} and a.year_part = #{yearPart}  and a.quarter_num = #{quarterNum} ORDER BY b.created_at DESC limit 1")
    public abstract List<String> getGradeTeacherNameList(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    /**
     * 获取班级使用课时
     * @param gradeId
     * @param studentId
     * @return
     */
    @Select("select IFNULL(sum(use_course_count),0) as use_course_count from student_grade where grade_id = #{gradeId} and student_id=#{studentId} and deleted = 0  and status<>3")
    public abstract Integer useCourseCount(@Param("gradeId") Long gradeId,@Param("studentId") Long studentId);

    /**
     * 获取所有使用课时
     * @return
     */
    //Select("select sum(use_course_count) as use_course_count from student_grade ${ew.customSqlSegment}")
    @Select("select sum(use_course_count) as use_course_count from student_grade where deleted = 0  and status<>3 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id = #{subjectsId}")
    public abstract Integer useAllCourseCount(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId);

    @Select("select * from (select a.id,a.student_id,a.grade_id,a.use_course_count,(select sum(buckle_class_house) from record_course as b where b.grade_id=a.grade_id and b.student_id =a.student_id) as use_count from student_grade as a) as t  where use_course_count<>use_count")
    public abstract List<Map<String,Object>> getTest();

    /**
     * 获取本学期使用课时
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "IFNULL( sum( t1.use_course_count ), 0 ) AS count ",
                    "FROM",
                    "student_grade AS t1",
                    "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
                    "WHERE",
                    "t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum} ",
                    "AND t1.subjects_id = ${subjectsId} ",
                    "AND t1.student_id = ${studentId} ",
                    "AND t1.lesson_type = ${lessonType} ",
                    "AND t1.year_class_id = ${yearClassId} ",
                    "AND t1.deleted = 0 ",
                    "AND t1.STATUS &lt;&gt; 3 ",
                    "AND t2.is_makeup = 0",
            "</script>"})
    public abstract Long getStudentUseCourseCount(@Param("yearPart") Integer yearPart,
                                                  @Param("quarterNum") Integer quarterNum,
                                                  @Param("subjectsId") Long subjectsId,
                                                  @Param("studentId") Long studentId,
                                                  @Param("lessonType") Integer lessonType,
                                                  @Param("yearClassId") Integer yearClassId
                                                  );

    @Select("select * from student_grade where school_id = ${schoolId} and student_id = ${studentId} and year_part = ${yearPart} and quarter_num = ${quarterNum} and subjects_id = ${subjectsId} and is_presort = 1 and status = 1")
    public Integer getIsPresortCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId);

    /**
     * 获取是否有在读
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select count(id) as count from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and student_id = ${studentId} and subjects_id = ${subjectsId} and lesson_type = ${lessonType} and status = 1 and reading_status =1 and deleted = 0")
    public Long getStudentReadingCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId,@Param("lessonType") Integer lessonType);


    /**
     * 获取老师名称
     * @param gradeId
     * @return
     */
    @Select("select t2.name from grade as t1 LEFT JOIN staff as t2 on t1.teacher_id = t2.id where t1.id = ${gradeId} limit 1")
    public String getTeacherNameForGradeId(@Param("gradeId") Long gradeId);

    /**
     * 获取最后一次分班在读信息
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select *,getStaffNameForId(teacher_id) as teacher_name from student_grade_view where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type =1 and reading_status =1 and deleted =0 ORDER BY id desc limit 1")
    public RecordCourse getLastReadingGrade(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId);

    /**
     * 获取最后一条数据，不区分is_last
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.grade_id,",
            "t1.class_type_id_new",
            "FROM",
            "student_grade AS t1",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.lesson_type = ${lessonType}",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "and t1.status &lt;&gt; 3",
            "ORDER BY",
            "t1.id DESC ",
            "LIMIT 1",
            "</script>"})
    public abstract StudentGrade getLastOne(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("studentId") Long studentId,
                                            @Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
                    "t1.* ",
                    "FROM",
                    "student_grade AS t1",
                    "INNER JOIN (",
                    "SELECT",
                      "MAX( id ) AS id ",
                    "FROM",
                      "student_grade ",
                    "WHERE",
                      "year_part = ${yearPart} ",
                      "AND quarter_num = ${quarterNum} ",
                      "AND lesson_type = 1 ",
                        "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                            "AND school_id = ${schoolId} ",
                        "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                      "AND subjects_id = ${subjectsId} ",
                    "</when>",
                      "AND deleted = 0 ",
                      "AND `status` &lt;&gt; 3 ",
                    "GROUP BY",
                      "student_id,",
                    "subjects_id ",
                    ") AS t2 ON t1.id = t2.id",
            "</script>"})
    public IPage<StudentGrade> getStudentGradeLastList(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId);


    @Select("select * from student_grade as t1 INNER JOIN (select max(id) as id from student_grade where year_part = ${yearPart} and `quarter_num` = ${quarterNum} and status<>3 and lesson_type =1 and deleted =0 GROUP BY student_id,subjects_id) as t2 on t1.id = t2.id where t1.is_last = 0")
    public List<StudentGrade> getNotIsLastList(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    @Select("select count(id) from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type = ${lessonType} and is_presort in(${presortStr}) and deleted =0 and `status`<>3")
    public Integer getPresortCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType,@Param("presortStr") String presortStr);

    @Select("select t1.* from student_grade as t1 INNER JOIN (select MAX(id) as id from student_grade where deleted =0 and `status`<>3 and year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type = ${lessonType} and student_id =${studentId} and subjects_id = ${subjectsId} and year_class_id = ${yearClassId}  GROUP BY student_id,subjects_id) as t2 on t1.id = t2.id limit 1")
    public StudentGrade getLastForMaxId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType,@Param("yearClassId") Integer yearClassId);

}
